package com.nstechs.commerce.transaction.productitem;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ProductItemDateTranfer {
	
	private static Logger logger = LoggerFactory.getLogger("productitem");
	private static String PRODUCT_ITEM_SQL = "call products.create_prodcut_item_info()";
	private static String CHECK_FOREIGN_KEY= "SET FOREIGN_KEY_CHECKS=0";
	private static String INSERT_PRODUCT_ITEM_SQL= "INSERT INTO products.productitem(sku, bar_code, created_date, rack_status, regular_price, retail_price, sale_price, sku_url, status, product_id, msrp, gender, from_age, to_age) values(?, ?, now(), ?, ?, ?, ?, '', ?, ?, ?, ?, ?, ?)";
	private static String INSERT_PRODUCTITEM_RACK= "INSERT INTO products.productitemrack(sku, rack_date, rack_status) select sku, now(), status from products.productitem";
	private static String PRODUCT_ATTRIBUTE_SQL = "SELECT pi.sku, pia.size, pia.season, pia.gender, pia.sales_volume, pia.length, pia.width, pia.height, pia.weight, pia.material_composition, pia.split_shipped, pa.year, pa.from_age, pa.color_name, pa.seo_keywords, pa.to_age, pa.for_age, pia.gender_description, pa.type FROM product_item_attribute pia INNER JOIN product_item pi ON (pia.sku = pi.sku and pi.status = 'A') INNER JOIN product_attribute pa ON pi.product_id = pa.product_id";
	private static String PRODUCT_ITEM_ERROR_SQL = "SELECT pi.*, p.id FROM products.productitem pi LEFT JOIN products.product p ON pi.product_id = p.id WHERE p.id IS NULL";
	private static String UPDATE_PRODUCT_ITEM_SQL = "UPDATE products.productitem pi LEFT JOIN products.product p ON pi.product_id = p.id SET pi.`status` = 'N' WHERE p.id IS NULL ";
	//private static String UPDATE_PRODUCT_ITEM__SQL = "UPDATE products.productitem pi LEFT JOIN products.product p ON pi.product_id = p.id SET pi.`status` = 'N' WHERE p.id IS NULL ";
	private  ConnectionUtils targetConnUtils = null;
	private  ConnectionUtils sourceConnUtils = null;
	
	public static void main(String[] args) {
		ProductItemDateTranfer tranfer = new ProductItemDateTranfer();
		tranfer.init();
		try {
			tranfer.tranferData4prudtcItem();
			tranfer.tranferData4prudtcItemAttr();
		} catch (Exception e) {
			logger.error(e.getMessage());
		}
	}
	
	public void init () {
		Properties properties = new Properties();
		try {
			properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("transaferjdbc.properties"));
			targetConnUtils = new ConnectionUtils(properties.getProperty("jdbc.target.user"), properties.getProperty("jdbc.target.password"), properties.getProperty("jdbc.target.url"));
			sourceConnUtils = new ConnectionUtils(properties.getProperty("jdbc.source.user"), properties.getProperty("jdbc.source.password"), properties.getProperty("jdbc.source.url"));
		} catch (IOException e) {
			logger.error(e.getMessage());
		}
	}
	
	public void tranferData4prudtcItem() throws SQLException {
		List<Object[]> productItems = new ArrayList<Object[]>();
		ResultSet rs = sourceConnUtils.executeQuery(PRODUCT_ITEM_SQL);
		int errorNum = 0;
		int sucessNum = 0;
		while (rs.next()) {
			Object[] productItem = new Object[]{rs.getString("sku"), rs.getString("upc"), "A".equals(rs.getString("status")) ? "Y" : "N",  rs.getDouble("list_price"), rs.getDouble("retail_price"), rs.getDouble("retail_price"), "A".equals(rs.getString("status")) ? "Y" : "N", rs.getInt("product_id"),  rs.getDouble("msrp"),  rs.getString("gender") == null ? "For Both" :  rs.getString("gender"),  rs.getString("from_age"), rs.getString("to_age")};
			if (rs.getString("product_id") == null) {
				errorNum ++;
				logger.info("products.productItem sku {}, bar_code {}, rack_status {}, regular_price {}, retail_price {}, sale_price{}, status {}, product_id {}, msrp {}, gender {}, form_age {}, to_age {} error:product delete ", productItem);
			} else {
				sucessNum++;
				logger.info("products.productItem sku {}, bar_code {}, rack_status {}, regular_price {}, retail_price {}, sale_price{}, status {}, product_id {}, msrp {}, gender {}, form_age {}, to_age {}", productItem);
				productItems.add(productItem);
			}
		}
		targetConnUtils.executeBatch(INSERT_PRODUCT_ITEM_SQL, productItems);
		
		writeErrorRecordsLog();
		targetConnUtils.execute(INSERT_PRODUCTITEM_RACK);
		targetConnUtils.execute("ALTER TABLE products.sku_sequence auto_increment=35123");
		logger.info("dataTranfer complete error records :" + errorNum + " sucess records : " + sucessNum);
		
	}
	
	public void test() {
		targetConnUtils.execute(INSERT_PRODUCTITEM_RACK);
	}
	//ALTER TABLE products.sku_sequence auto_increment=34123;
	//SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'sku_sequence';
	//ALTER TABLE warehouses.container_sequence auto_increment=10677;
	//SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'container_sequence';
	
	public void tranferData4prudtcItemAttr() throws SQLException {
		final String insertAttrSql = "insert into products.skuattributevalue(attribute_value, attribute_id, sku) select ?, id, ? from productattribute where attribute_name = ?";
		List<Object[]> productItemAttrs = new ArrayList<Object[]>();
		ResultSet rs = sourceConnUtils.executeQuery(PRODUCT_ATTRIBUTE_SQL);
		int totalRecords = 0;
		while (rs.next()) {
			totalRecords++;
			if(rs.getString("sku") != null) {
				if (rs.getString("type") != null)
				productItemAttrs.add(new Object[]{ rs.getString("type"), rs.getString("sku"), "Type"});
				if (rs.getString("size") != null)
				productItemAttrs.add(new Object[]{ rs.getString("size"), rs.getString("sku"), "Size"});
				if (rs.getString("color_name") != null)
				productItemAttrs.add(new Object[]{ rs.getString("color_name"), rs.getString("sku"), "Color Name"});
				if (rs.getString("year") != null)
				productItemAttrs.add(new Object[]{ rs.getString("year"), rs.getString("sku"), "Year"});
				if (rs.getString("season") != null)
				productItemAttrs.add(new Object[]{ rs.getString("season"), rs.getString("sku"), "Season"});
				if (rs.getString("length") != null)
				productItemAttrs.add(new Object[]{ rs.getString("length"), rs.getString("sku"), "Length"});
				if (rs.getString("width") != null)
				productItemAttrs.add(new Object[]{ rs.getString("width"), rs.getString("sku"), "Width"});
				if (rs.getString("height") != null)
				productItemAttrs.add(new Object[]{ rs.getString("height"), rs.getString("sku"), "Height"});
				if (rs.getString("weight") != null)
				productItemAttrs.add(new Object[]{ rs.getString("weight"), rs.getString("sku"), "Weight"});
				if (rs.getString("material_composition") != null)
				productItemAttrs.add(new Object[]{ rs.getString("material_composition"), rs.getString("sku"), "Material Composition"});
				if (rs.getString("split_shipped") != null)
				productItemAttrs.add(new Object[]{ rs.getString("split_shipped"), rs.getString("sku"), "Split Shipped"});
			}
		}
		logger.info("product attribute records : " + totalRecords + " attr_item : " + productItemAttrs.size());
		targetConnUtils.executeBatch(insertAttrSql, productItemAttrs);
	}
	
	public void writeErrorRecordsLog() throws SQLException {
		ResultSet rs = targetConnUtils.executeQuery(PRODUCT_ITEM_ERROR_SQL);
		while (rs.next()) {
			Object[] productItem = new Object[]{rs.getString("sku"), rs.getString("bar_code"), rs.getString("status"),  rs.getDouble("regular_price"), rs.getDouble("retail_price"), rs.getDouble("retail_price"), rs.getString("status"), rs.getInt("product_id"),  rs.getDouble("msrp"),  rs.getString("gender"),  rs.getString("from_age"), rs.getString("to_age")};
			logger.info("products.productItem sku {}, bar_code {}, rack_status {}, regular_price {}, retail_price {}, sale_price{}, status {}, product_id {}, msrp {}, gender {}, form_age {}, to_age {} error: product delete", productItem);
		}
		targetConnUtils.execute(UPDATE_PRODUCT_ITEM_SQL);
	}
}
